IF OBJECT_ID('dbo.vwAgreementRolesandEfforts') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwAgreementRolesandEfforts
    IF OBJECT_ID('dbo.vwAgreementRolesandEfforts') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwAgreementRolesandEfforts >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwAgreementRolesandEfforts >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwAgreementRolesandEfforts
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 7/17/2007 
--
-- Description: View to Efforts and Roles for Agreements
--
-- Used BY Procedure/Report: (MOST)
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/17/07     KC        Created
-- 8/10/07     KC        Changed to use SvcEntityId to be joined to 
--                            Agreement Fact table & flattenedd resultset 
--    to display PendingEffort and CommittedEffort as columns in the same row.
-- 8/22/07     KC        Finalized.
--
-- =======================================================================
CREATE VIEW dbo.vwAgreementRolesandEfforts
AS
     SELECT    EFF.SvcEntityId,
               EFF.AdmPersonId,
               EFF.StaffedRoleName,
               EFF.SequenceYear,
               MAX(CASE EFF.BudgetViewTypeId WHEN 1 THEN EFF.Effort ELSE 0.00 END) AS PendingEffort,
               MAX(CASE EFF.BudgetViewTypeId WHEN 3 THEN EFF.Effort ELSE 0.00 END) AS CommittedEffort
     FROM(     SELECT    SPM.SvcEntityId as SvcEntityId,
                         SPM.AdmPersonId as AdmPersonId,
                         PT.Name AS StaffedRoleName,
                         PEY.SequenceYear AS SequenceYear,
                         PEY.AgrBudgetViewTypeId AS BudgetViewTypeId,
                         PEY.PercentageEffort AS Effort
               FROM      dbo.SvcEntity_AdmStaffedPerson_Map SPM
                         JOIN dbo.AgrStaffedPersonEffortYear PEY ON SPM.Id = PEY.SvcEntityStaffedPersonMapId 
                         JOIN dbo.AdmStaffedPersonType PT ON SPM.AdmStaffedPersonTypeId = PT.Id
               WHERE     PEY.AgrBudgetViewTypeId in ( 1, 3 ) -- (Pending, Fully Executed/Awarded)
           ) EFF
     GROUP BY EFF.SvcEntityId,
               EFF.AdmPersonId,
               EFF.StaffedRoleName,
               EFF.SequenceYear
go

IF OBJECT_ID('dbo.vwAgreementRolesandEfforts') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwAgreementRolesandEfforts >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwAgreementRolesandEfforts >>>'
go
